<?php
namespace api\service;

use Yii;

class UserService
{
    /**
     * pub_user跟edu_user 连表获取用户的信息
     * @param $uid
     * @return array|false
     */
    public function userPubMin($uid)
    {
        $sql = "SELECT pub.id AS pub_id, pub.openid AS pub_openid, pub.nickname, pub.headimgurl, pub.unionid,mini.id AS mini_id, mini.openid AS mini_openid, mini.mall_total_fee, mini.mall_put_money, mini.mall_id FROM pub_user AS pub LEFT JOIN edu_user  AS mini ON pub.unionid = mini.unionid WHERE mini.mall_is = 0 AND pub.id = {$uid}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 统计今天的结算收益
     * @param $mini_id
     * @return false|null|string
     */
    public function userTodayProfit($mini_id)
    {
        $todayStart = strtotime(date("Y-m-d 00:00:00"), time());
        $todayEnd = $todayStart + 24 * 3600;
        $sql = "SELECT COUNT(amount) FROM mall_bill WHERE uid = {$mini_id} AND status = 1 AND finish_time > {$todayStart} AND finish_time < {$todayEnd}";
        return Yii::$app->db->createCommand($sql)->queryScalar();
    }

    /**
     * 未到账的收益
     * @param $mini_id
     * @return false|null|string
     */
    public function userWaitProfit($mini_id)
    {
        $sql = "SELECT COUNT(amount) FROM mall_bill WHERE uid = {$mini_id} AND status = 2";
        return Yii::$app->db->createCommand($sql)->queryScalar();
    }

    /**
     * 收益记录
     * @param $andWhere
     * @param $page
     * @param $pre_page
     * @return array
     */
    public function userProfit($andWhere, $page, $pre_page)
    {
        $count = $this->profitCount($andWhere);
        $data = ['total' => 0, 'total_page' => 0, 'page' => 1, 'pre_page' => $pre_page, 'lists' => []];
        if ($count) {
            $total_page = ceil( $count / $pre_page);
            $offset = ($page - 1) * $pre_page;
            $sql = " SELECT out_trade_no, status, direct, direct_text, amount, create_time, finish_time, uid, sid, mid FROM mall_bill WHERE {$andWhere} ORDER BY create_time DESC LIMIT {$offset}, {$pre_page} ";
            $res = Yii::$app->db->createCommand($sql)->queryAll();
            foreach ($res as $key => $value ) {
                //user_name 从edu_user 中获取
                $sql_user = "SELECT id, nickname FROM edu_user WHERE id = {$value['sid']}";
                $user = Yii::$app->db->createCommand($sql_user)->queryOne();
                $value['user_name'] = $user['nickname'];
                //产品表中获取封面图, 标题，
                //从mall_order_list中获取产品数量价格
                $sql_order_list = "SELECT l.pid, l.out_trade_no, l.count, l.price AS pay, p.img AS image, p.title AS goods_name FROM mall_order_list AS l LEFT JOIN mall_product AS p ON l.pid = p.id WHERE out_trade_no = '{$value['out_trade_no']}'";
                $order_list = Yii::$app->db->createCommand($sql_order_list)->queryAll();
                $value['goods'] = $order_list;
                $res[$key] = $value;
            }
            $data = ['total' => $count, 'total_page' => $total_page, 'page' => $page, 'pre_page' => $pre_page, 'lists' => $res];
        }
        return $data;
    }

    /**
     * 统计记录条数
     * @param $andWhere
     * @return false|null|string
     */
    private function profitCount($andWhere)
    {
        $sql = "SELECT COUNT(*) FROM mall_bill WHERE {$andWhere} ";
        return Yii::$app->db->createCommand($sql)->queryScalar();
    }

    /**
     * 获取其中一条用户信息
     * @param $where
     * @return array|false
     */
    public function userOne($where)
    {
        $sql = "SELECT mall_total_fee, id, openid, unionid, mall_id FROM  edu_user WHERE {$where} ";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 统计用户提现成功的金额
     * @param $mini_id
     * @return false|null|string
     */
    public function userAppear($mini_id)
    {
        $sql = "SELECT COUNT(amount) FROM mall_appear WHERE uid = {$mini_id} AND status = 2 ";
        return Yii::$app->db->createCommand($sql)->queryScalar();
    }

    /**
     * 用户发起提现
     * @param $map
     * @return bool
     */
    public function userCashAppear($map)
    {
        $db = Yii::$app->db;
        $transaction = $db->beginTransaction();
        try{
            $db->createCommand()->insert('mall_appear', $map)->execute();
            //同时将金额放入到冻结中
            $sql = "UPDATE edu_user SET mall_total_fee = mall_total_fee - {$map['amount']}, mall_put_money = mall_put_money + {$map['amount']} WHERE id = {$map['uid']}";
            $db->createCommand($sql)->execute();

            $transaction->commit();
            return true;
        } catch (\Exception $e) {
            var_dump($e);
            $transaction->rollBack();
            return false;
        }
    }

    /**
     * 用户提现记录
     * @param $map
     * @param $page
     * @param $pre_page
     * @return array
     */
    public function userAppearDetail($map, $page, $pre_page = 10)
    {
        $count = $this->cashDetail($map);
        $data = ['total' => 0, 'total_page' => 0, 'page' => 1, 'pre_page' => $pre_page, 'lists' => []];
        if ($count) {
            $offset = ($page - 1 ) * $pre_page;
            $total_page = ceil($count / $pre_page);
            $sql = "SELECT * FROM mall_appear WHERE {$map} ORDER BY create_time DESC LIMIT {$offset}, {$pre_page} ";
            $res = Yii::$app->db->createCommand($sql)->queryAll();

            $data = ['total' => $count, 'total_page' => $total_page, 'page' => $page, 'pre_page' => $pre_page, 'lists' => $res];
        }
        return $data;
    }

    private function cashDetail($map)
    {
        $sql = "SELECT COUNT(*) FROM mall_appear WHERE {$map}";
        return Yii::$app->db->createCommand($sql)->queryScalar();
    }

    /**
     * 查找小程序用户信息
     * @param $uid
     * @return array|false
     */
    public function userMin($uid)
    {
        $sql = "SELECT mini.unionid, mini.id AS mini_id, mini.openid AS mini_openid, mini.mall_total_fee, mini.mall_put_money, mini.mall_id FROM edu_user  AS mini  WHERE mini.mall_is = 0 AND mini.id = {$uid}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }
}